package cn.threewaters.qtyy.util.excel;

import java.math.BigDecimal;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import cn.threewaters.qtyy.util.hutool.core.date.DateUtil;

/**
 * 基于POI实现的Excel工具类
 * 
 * @ClassName: ExcelUtil
 * @Description: 基于POI实现的Excel工具类
 * @author: zhangmiao@udesk.cn
 * @date: 2017年2月13日 上午11:16:43
 */
public class ExcelUtil {

	/**
	 * 创建一个工作簿
	 * 
	 * @Title: createWorkbook
	 * @Description: 创建一个工作簿
	 * @return
	 * @return: HSSFWorkbook
	 */
	public static HSSFWorkbook createWorkbook() {
		HSSFWorkbook workbook = new HSSFWorkbook();
		return workbook;
	}

	public static HSSFCellStyle initColumnHeadStyle(HSSFWorkbook wb) {
		HSSFCellStyle columnHeadStyle = wb.createCellStyle();
		HSSFFont columnHeadFont = wb.createFont();
		columnHeadFont.setFontName("隶书");
		columnHeadFont.setFontHeightInPoints((short) 20);
		columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		columnHeadStyle.setFont(columnHeadFont);
		columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		columnHeadStyle.setLocked(true);
		// columnHeadStyle.setWrapText(true);
		columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
		columnHeadStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 边框的大小

		columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
		columnHeadStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 边框的大小

		columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 边框的大小

		columnHeadStyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		columnHeadStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 边框的大小

		// 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）
		columnHeadStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
		// columnHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		return columnHeadStyle;
	}

	public static HSSFCellStyle initColumnHead2Style(HSSFWorkbook wb) {
		HSSFCellStyle columnHeadStyle = wb.createCellStyle();
		HSSFFont columnHeadFont = wb.createFont();
		columnHeadFont.setFontName("宋体");
		columnHeadFont.setFontHeightInPoints((short) 12);
		columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		columnHeadStyle.setFont(columnHeadFont);
		columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		columnHeadStyle.setLocked(true);
		// columnHeadStyle.setWrapText(true);
		columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
		columnHeadStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 边框的大小

		columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
		columnHeadStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 边框的大小

		columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 边框的大小

		columnHeadStyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		columnHeadStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 边框的大小

		// 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）
		columnHeadStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
		// columnHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		return columnHeadStyle;
	}

	public static HSSFCellStyle initColumnCenterstyle(HSSFWorkbook wb) {
		HSSFFont font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 10);
		HSSFCellStyle centerstyle = wb.createCellStyle();
		centerstyle.setFont(font);
		centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居左
		centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		// centerstyle.setWrapText(true);

		centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
		centerstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
		centerstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 边框的大小

		centerstyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 边框的大小
		return centerstyle;
	}

	public static HSSFCellStyle initColumnCenterstyleHigh(HSSFWorkbook wb) {
		HSSFFont font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 10);
		HSSFCellStyle centerstyle = wb.createCellStyle();
		centerstyle.setFont(font);
		centerstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		centerstyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居左
		centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		// centerstyle.setWrapText(true);

		centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
		centerstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
		centerstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 边框的大小

		centerstyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 边框的大小
		return centerstyle;
	}

	public static HSSFCellStyle initColumnCenterstyleLow(HSSFWorkbook wb) {
		HSSFFont font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 10);
		HSSFCellStyle centerstyle = wb.createCellStyle();
		centerstyle.setFont(font);
		centerstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		centerstyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
		centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居左
		centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		// centerstyle.setWrapText(true);

		centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
		centerstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
		centerstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 边框的大小

		centerstyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 边框的大小
		return centerstyle;
	}

	public static HSSFCellStyle initColumnCenter2style(HSSFWorkbook wb) {
		HSSFFont font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 10);
		HSSFCellStyle centerstyle = wb.createCellStyle();
		centerstyle.setFont(font);
		centerstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居左
		centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
		// centerstyle.setWrapText(true);

		centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
		centerstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
		centerstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 边框的大小

		centerstyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 边框的大小
		return centerstyle;
	}

	public static HSSFCellStyle initColumnCenter3style(HSSFWorkbook wb) {
		HSSFFont font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 10);
		HSSFCellStyle centerstyle = wb.createCellStyle();
		centerstyle.setFont(font);
		centerstyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 左右居左
		centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		// centerstyle.setWrapText(true);

		centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
		centerstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
		centerstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 边框的大小

		centerstyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 边框的大小
		return centerstyle;
	}

	public static HSSFCellStyle initMonitorColumnCenterstyle(HSSFWorkbook wb) {
		HSSFFont font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 10);
		HSSFCellStyle centerstyle = wb.createCellStyle();
		centerstyle.setFont(font);
		centerstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居左
		centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
		// centerstyle.setWrapText(true);

		centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
		centerstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
		centerstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 边框的大小

		centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 边框的大小

		centerstyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
		centerstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 边框的大小
		return centerstyle;
	}

	/**
	 * 创建一个sheet页
	 * 
	 * @Title: createSheet
	 * @Description: 创建一个sheet页
	 * @param workbook
	 * @param sheetname
	 * @return
	 * @return: HSSFSheet
	 */
	public static HSSFSheet createSheet(HSSFWorkbook workbook, String sheetname) {
		HSSFSheet sheet = null;
		if (sheetname == null || sheetname.equals("")) {
			sheet = workbook.createSheet();
		} else {
			sheet = workbook.createSheet(sheetname);
		}
		return sheet;
	}

	public static HSSFRow createRow(HSSFSheet sheet, int rowIndex) {
		HSSFRow row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		return row;
	}

	public static void createCell(HSSFRow row, int columnIndex, Object value, HSSFCellStyle cellStyle) {
		HSSFCell cell = row.getCell(columnIndex);
		if (cell == null) {
			cell = row.createCell(columnIndex);
		}
		if (value == null) {
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue("");
		} else if (value instanceof Date) {
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue(DateUtil.formatDateTime((Date) value));
		} else if (value instanceof Long || value instanceof Double || value instanceof Float
				|| value instanceof Integer || value instanceof BigDecimal) {
			try {
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				cell.setCellValue(Double.parseDouble(String.valueOf(value)));
			} catch (Exception e) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				cell.setCellValue(String.valueOf(value));
			}
		} else {
			try {
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				cell.setCellValue(Double.parseDouble(String.valueOf(value)));
			} catch (Exception e) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				cell.setCellValue(String.valueOf(value));
			}
		}
		if (cellStyle != null) {
			cell.setCellStyle(cellStyle);
		}

	}

	public static void writeCell(HSSFSheet sheet, int rowIndex, int columnIndex, Object value,
			HSSFCellStyle cellStyle) {
		createCell(createRow(sheet, rowIndex), columnIndex, value, cellStyle);
	}

	public static void mergeCell(HSSFSheet sheet, int rowBegin, int rowEnd, int columnBegin, int columnEnd,
			Object value, HSSFCellStyle cellStyle) {
		for (int i = rowBegin; i <= rowEnd; i++) {
			HSSFRow row = createRow(sheet, i);
			for (int j = columnBegin; j <= columnEnd; j++) {
				createCell(row, j, null, cellStyle);
			}
		}
		if (rowBegin == rowEnd && columnBegin == columnEnd) {
			HSSFRow row = createRow(sheet, rowBegin);
			createCell(row, columnBegin, value, cellStyle);
		} else {
			CellRangeAddress cra = new CellRangeAddress(rowBegin, rowEnd, columnBegin, columnEnd);
			sheet.addMergedRegion(cra);
			HSSFRow row = createRow(sheet, rowBegin);
			createCell(row, columnBegin, value, cellStyle);
		}

	}

	public static void freezePane(HSSFSheet sheet, int columnCount, int rowCount) {
		sheet.createFreezePane(columnCount, rowCount);
	}

	public static void autoColumnSize(HSSFSheet sheet, int index, int max, int min) {
		sheet.autoSizeColumn(index);
		int columnWidth = sheet.getColumnWidth(index);
		if (columnWidth < min) {
			sheet.setColumnWidth(index, min);
		} else if (columnWidth > max) {
			sheet.setColumnWidth(index, max);
		}
	}

}
